package org.chipu.shared.db;

import java.io.File;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.*;
import javax.sql.DataSource;
import org.chipu.shared.db.proxool.ProxoolDataSource;
import org.chipu.shared.interfaces.Loggeable;
import org.chipu.shared.util.singletons.*;

public final class DatabaseManager implements Loggeable {
	private final DataSource ds;
	private Logger logger = Logger.getLogger(getClass().getSimpleName());

	/** It uses the servlet container resources */
	public DatabaseManager(String dataSourceName) throws NamingException {
		// IMPROVE when java6 use InitialContext.doLookup("java:comp/env");
		this((DataSource) ((Context) new InitialContext().lookup("java:comp/env")).lookup("jdbc/"
			+ dataSourceName));
	}

	/** It uses proxool database pool to create a MySQL DataSource */
	public DatabaseManager(String mysqlServer, int mysqlPort, String dbName, String user, String pass,
		int maxConnections) throws ClassNotFoundException {
		this(new ProxoolDataSource(dbName, mysqlServer, mysqlPort, user, pass, maxConnections));
	}

	/** It uses proxool database pool to create a DataSource */
	public DatabaseManager(String driverClassname, String driverProtocol, String dbServer, int dbPort,
		String dbName, String user, String pass, int maxConnections) throws ClassNotFoundException {
		this(new ProxoolDataSource(driverClassname, driverProtocol, dbName, dbServer, dbPort, user, pass,
			maxConnections));
	}

	public DatabaseManager(DataSource dataSource) {
		ds = dataSource;
	}

	@Override
	public void setLogger(Logger logger) {
		if (logger != null)
			this.logger = logger;
		if (ds instanceof Loggeable)
			((Loggeable) ds).setLogger(logger);
	}

	private Connection getConnection() throws SQLException {
		return ds.getConnection();
	}

	private void release(Connection conn) {
		try {
			conn.close();
		} catch(SQLException ex) {
			logger.log(Level.SEVERE, "Error closing connection", ex);
		}
	}

	public static String getDBType(Class<?> c) {
		if (Long.class.equals(c))
			return "bigint";
		else if (Integer.class.equals(c))
			return "int";
		else if (Short.class.equals(c))
			return "smallint";
		else if (Byte.class.equals(c))
			return "tinyint";
		else if (Boolean.class.equals(c))
			// return "TINYINT(1)"
			return "bit";
		else if (Date.class.equals(c))
			return "datetime";
		else if (String.class.equals(c))
			// IMPROVE choose char, varchar or text
			return "text";
		System.err.println("Class not recognized: " + c);
		return "text";
	}

	// ----------

	public String getStringDataFromDB(String queryName, String sql) throws Exception {
		logQuery(queryName, sql);
		return getStringDataFromDB(sql);
	}

	public String getStringDataFromDB(final String sql) throws Exception {
		return getDataFromDB(new STExecuter<String>() {
			@Override
			public String execute(Statement st) throws SQLException {
				ResultSet rs = st.executeQuery(sql);
				if (rs.next())
					return rs.getString(1);
				return "";
			}
		});
	}

	public String[] getStringsDataFromDB(String queryName, String sql) throws Exception {
		logQuery(queryName, sql);
		return getStringsDataFromDB(sql);
	}

	public String[] getStringsDataFromDB(final String sql) throws Exception {
		return getDataFromDB(new STExecuter<String[]>() {
			@Override
			public String[] execute(Statement st) throws SQLException {
				List<String> list = new ArrayList<String>();
				ResultSet rs = st.executeQuery(sql);
				while(rs.next())
					list.add(rs.getString(1));
				return list.toArray(new String[list.size()]);
			}
		});
	}

	/** @return null if the value is not found */
	public Boolean getBooleanDataFromDB(final String sql) throws Exception {
		return getDataFromDB(new STExecuter<Boolean>() {
			@Override
			public Boolean execute(Statement st) throws SQLException {
				ResultSet rs = st.executeQuery(sql);
				if (rs.next())
					return rs.getBoolean(1);
				return null;
			}
		});
	}

	/** @return -1 if the value is not found */
	public Integer getIntDataFromDB(String queryName, String sql) throws Exception {
		logQuery(queryName, sql);
		return getIntDataFromDB(sql);
	}

	/** @return -1 if the value is not found */
	public Integer getIntDataFromDB(final String sql) throws Exception {
		return getDataFromDB(new STExecuter<Integer>() {
			@Override
			public Integer execute(Statement st) throws SQLException {
				ResultSet rs = st.executeQuery(sql);
				if (rs.next())
					return rs.getInt(1);
				return -1;
			}
		});
	}

	/** @return -1 if the value is not found */
	public Long getLongDataFromDB(final String sql) throws Exception {
		return getDataFromDB(new STExecuter<Long>() {
			@Override
			public Long execute(Statement st) throws SQLException {
				ResultSet rs = st.executeQuery(sql);
				if (rs.next())
					return rs.getLong(1);
				return -1l;
			}
		});
	}

	/** @return -1 if the value is not found */
	public Float getFloatDataFromDB(String queryName, String sql) throws Exception {
		logQuery(queryName, sql);
		return getFloatDataFromDB(sql);
	}

	/** @return -1 if the value is not found */
	public Float getFloatDataFromDB(final String sql) throws Exception {
		return getDataFromDB(new STExecuter<Float>() {
			@Override
			public Float execute(Statement st) throws SQLException {
				ResultSet rs = st.executeQuery(sql);
				if (rs.next())
					return rs.getFloat(1);
				return -1f;
			}
		});
	}

	public Map<String, String> getMapDataFromDB(String queryName, String sql) throws Exception {
		logQuery(queryName, sql);
		return getMapDataFromDB(sql);
	}

	public Map<String, String> getMapDataFromDB(final String sql) throws Exception {
		return getDataFromDB(new STExecuter<Map<String, String>>() {
			@Override
			public Map<String, String> execute(Statement st) throws SQLException {
				Map<String, String> datas = new HashMap<String, String>();
				ResultSet rs = st.executeQuery(sql);
				while(rs.next())
					datas.put(rs.getString(1), rs.getString(2));
				return datas;
			}
		});
	}

	public Map<String, Integer> getMapSIDataFromDB(String queryName, String sql) throws Exception {
		logQuery(queryName, sql);
		return getMapSIDataFromDB(sql);

	}

	public Map<String, Integer> getMapSIDataFromDB(final String sql) throws Exception {
		return getDataFromDB(new STExecuter<Map<String, Integer>>() {
			@Override
			public Map<String, Integer> execute(Statement st) throws SQLException {
				ResultSet rs = st.executeQuery(sql);
				Map<String, Integer> c = new HashMap<String, Integer>();
				while(rs.next())
					c.put(rs.getString(1), rs.getInt(2));
				return c;
			}
		});
	}

	public Map<Integer, String> getMapISDataFromDB(String queryName, String sql) throws Exception {
		logQuery(queryName, sql);
		return getMapISDataFromDB(sql);
	}

	public Map<Integer, String> getMapISDataFromDB(final String sql) throws Exception {
		return getDataFromDB(new STExecuter<Map<Integer, String>>() {
			@Override
			public Map<Integer, String> execute(Statement st) throws SQLException {
				ResultSet rs = st.executeQuery(sql);
				Map<Integer, String> c = new HashMap<Integer, String>();
				while(rs.next())
					c.put(rs.getInt(1), rs.getString(2));
				return c;
			}
		});
	}

	// -----
	public void executeBatchCommand(String cmd) {
		executeBatchCommand(cmd, true);
	}

	public void executeBatchCommand(String cmd, boolean specifyDb) {
		if (System.getProperty("os.name").startsWith("Windows"))
			WindowsUtils.runCommand(getCommonBatch(specifyDb) + " -e \"" + cmd + "\"");
		else
			LinuxUtils.runCommand(getCommonBatch(specifyDb) + " -e \"" + cmd + "\"");
	}

	public void executeBatchFile(File file) {
		if (System.getProperty("os.name").startsWith("Windows"))
			WindowsUtils.runCommand(getCommonBatch() + " < " + file.getAbsolutePath());
		else
			LinuxUtils.runCommand(getCommonBatch() + " < " + file.getAbsolutePath());
	}

	private String getCommonBatch() {
		return getCommonBatch(true);
	}

	private String getCommonBatch(boolean specifyDb) {
		assert ds instanceof ProxoolDataSource;
		ProxoolDataSource pds = (ProxoolDataSource) ds;
		return "mysql -u" + pds.getUser()
			+ (Utils.isNullOrEmpty(pds.getPassword())? "": " -p" + pds.getPassword())
			+ (specifyDb? " " + pds.getDB(): "");
	}

	public void execute(String queryName, String sql) throws Exception {
		logQuery(queryName, sql);
		execute(sql);
	}

	public void execute(final String sql) throws Exception {
		try {
			getDataFromDB(new STExecuter<Void>() {
				@Override
				public Void execute(Statement st) throws SQLException {
					// st.execute(sql);
					st.executeUpdate(sql);
					return null;
				}
			});
		} catch(SQLException ex) {
			logger.log(Level.SEVERE, "Error executing query: " + sql, ex);
			throw ex;
		}
	}

	/** @return last_insert_id */
	public int insert(String queryName, String sql) throws Exception {
		logQuery(queryName, sql);
		return insert(sql);
	}

	/** @return last_insert_id */
	public int insert(final String sql) throws Exception {
		return getDataFromDB(new STExecuter<Integer>() {
			@Override
			public Integer execute(Statement st) throws SQLException {
				st.execute(sql);
				ResultSet rs = st.executeQuery("SELECT LAST_INSERT_ID()");
				return rs.next()? rs.getInt(1): -1;
			}
		});
	}

	public boolean deleteLongs(final String table, final Iterable<Long> ids) throws Exception {
		return deleteLongs(table, ids, "id");
	}

	public boolean deleteLongs(final String table, final Iterable<Long> ids, String idname) throws Exception {
		return getPreparedDataFromDB("DELETE FROM " + table + " WHERE " + idname + "=?",
			new PSExecuter<Boolean>() {
				@Override
				public Boolean execute(PreparedStatement ps) {
					boolean ret = true;
					for(Long id : ids)
						try {
							ps.setLong(1, id);
							ps.executeUpdate();
						} catch(SQLException ex) {
							logger.log(Level.WARNING, "Error deleting object from table " + table
								+ " with id=" + id, ex);
							ret = false;
						}
					return ret;
				}
			});
	}

	public boolean deleteInts(final String table, final Iterable<Integer> ids) throws Exception {
		return deleteInts(table, ids, "id");
	}

	public boolean deleteInts(final String table, final Iterable<Integer> ids, String idname)
		throws Exception {
		return getPreparedDataFromDB("DELETE FROM " + table + " WHERE " + idname + "=?",
			new PSExecuter<Boolean>() {
				@Override
				public Boolean execute(PreparedStatement ps) {
					boolean ret = true;
					for(Integer id : ids)
						try {
							ps.setLong(1, id);
							ps.executeUpdate();
						} catch(SQLException ex) {
							logger.log(Level.WARNING, "Error deleting object from table " + table
								+ " with id=" + id, ex);
							ret = false;
						}
					return ret;
				}
			});
	}

	private void logQuery(String name, String sql) {
		if (name != null && !name.equals(""))
			logger.finest(name + ": " + sql);
	}

	public <O> O getDataFromDB(STExecuter<O> executor) throws Exception {
		Connection conn = getConnection();
		Statement st = null;
		try {
			st = conn.createStatement();
			return executor.execute(st);
		} finally {
			if (st != null)
				try {
					st.close();
				} catch(SQLException ex) {
					logger.log(Level.WARNING, "Error closing db statement", ex);
				}
			release(conn);
		}
	}

	public <O> O getPreparedDataFromDB(String sql, PSExecuter<O> executor) throws Exception {
		Connection conn = getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			return executor.execute(ps);
			// TODO when java6 use this to show the sql query
			// } catch(SQLException ex) {
			// throw new MysqlException(sql, "Database call failed", ex);
		} finally {
			if (ps != null)
				try {
					ps.close();
				} catch(SQLException ex) {
					logger.log(Level.WARNING, "Error closing db preparedStatement", ex);
				}
			release(conn);
		}
	}

	/*
	 * TODO catch(NoSuchServiceException usEx){throw new FileUploadException("Kernel not initialized", usEx);
	 */

	/** Not supported in mysql 4 */
	public Collection<String> getTableNamesStartingBy(String prefix, String dbName) throws Exception {
		final String sql = "SHOW tables WHERE tables_in_" + dbName + " LIKE '" + prefix + "%'";
		logger.finest("GetTableNamesStartingBy: " + sql);
		return getDataFromDB(new STExecuter<Collection<String>>() {
			@Override
			public Collection<String> execute(Statement st) throws SQLException {
				Set<String> names = new HashSet<String>();
				ResultSet rs = st.executeQuery(sql);
				while(rs.next())
					names.add(rs.getString(1));
				return names;
			}
		});
	}

	public List<String> getTableList() throws SQLException {
		Connection conn = getConnection();
		try {
			DatabaseMetaData metadata = conn.getMetaData();
			ResultSet tableNames = metadata.getTables(null, "%", "%", new String[]{"TABLE"});
			List<String> tableList = new ArrayList<String>();
			while(tableNames.next())
				tableList.add(tableNames.getString("TABLE_NAME"));
			return tableList;
		} finally {
			conn.close();
		}
	}

	public boolean hasColumn(final String table, final String column) throws Exception {
		return getDataFromDB(new STExecuter<Boolean>() {
			@Override
			public Boolean execute(Statement st) throws SQLException {
				ResultSet rs = st.executeQuery("describe " + table);
				// ResultSetMetaData rsmd = rs.getMetaData();
				while(rs.next())
					// for(int i = 1; i <= rsmd.getColumnCount(); i++)
					// if (column.equals(rsmd.getColumnName(i)))
					if (column.equals(rs.getString(1)))
						return true;
				return false;
			}
		});
	}

	/**
	 * Create the column.<br>
	 * To use when the database structure changes.
	 */
	public boolean addColumn(String table, String column, String type) {
		final String sql = "ALTER TABLE " + table + " ADD " + column + " " + type;
		Connection conn = null;
		Statement st = null;
		try {
			conn = getConnection();
			st = conn.createStatement();
			st.execute(sql);
			logger.info("Created " + table + "." + column + " (" + type + ")");
			return true;
		} catch(Exception ex) {
			logger.log(Level.WARNING, "Create column sentence: " + sql, ex);
			return false;
		} finally {
			if (st != null)
				try {
					st.close();
				} catch(SQLException ex) {
					logger.log(Level.WARNING, "Error closing db statement", ex);
				}
			if (conn != null)
				release(conn);
		}
	}

	public void clearDb() throws Exception {
		getDataFromDB(new STExecuter<Void>() {
			@Override
			public Void execute(Statement st) throws SQLException {
				for(String tableName : getTableList())
					st.executeUpdate("DELETE FROM " + tableName);
				return null;
			}
		});
	}

	public boolean dropTable(String table) {
		try {
			execute("DROP TABLE IF EXISTS " + table);
			return true;
		} catch(Exception ex) {
			logger.log(Level.SEVERE, "", ex);
			return false;
		}
	}

	public boolean existsTable(final String table) {
		final String sql = "SHOW TABLES";
		try {
			return getDataFromDB(new STExecuter<Boolean>() {
				@Override
				public Boolean execute(Statement st) throws SQLException {
					ResultSet rs = st.executeQuery(sql);
					while(rs.next())
						if (rs.getString(1).equals(table))
							return true;
					return false;
				}
			});
		} catch(Exception ex) {
			logger.log(Level.SEVERE, "", ex);
			return false;
		}
	}

	public boolean existsTable2(String tabla) {
		Connection conn = null;
		try {
			conn = getConnection();
			ResultSet tablas = conn.getMetaData().getTables(null, null, tabla, new String[]{"TABLE"});
			return tablas.next();
		} catch(SQLException ex) {
			logger.log(Level.SEVERE, "", ex);
			return false;
		} finally {
			release(conn);
		}
	}

	public boolean isTableEmpty(String table) {
		// using SQL_CALC_FOUND_ROWS with limit && SELECT FOUND_ROWS()
		final String sql = "SELECT count(*) FROM " + table;
		try {
			return getDataFromDB(new STExecuter<Boolean>() {
				@Override
				public Boolean execute(Statement st) throws SQLException {
					ResultSet rs = st.executeQuery(sql);
					if (rs.next())
						return rs.getInt(1) == 0;
					return true;
				}
			});
		} catch(Exception ex) {
			logger.log(Level.SEVERE, "", ex);
			return true;
		}
	}

	public boolean clearTable(String table) {
		final String sql = "DROP TABLE " + table;
		try {
			getDataFromDB(new STExecuter<Void>() {
				@Override
				public Void execute(Statement st) throws SQLException {
					st.execute(sql);
					return null;
				}
			});
			return true;
		} catch(Exception ex) {
			logger.log(Level.SEVERE, "", ex);
			return false;
		}
	}

	// -----
	public static boolean existsDatabase(String driverClassName, String server, int port, String user,
		String passwd, String database) throws ClassNotFoundException, SQLException {
		Class.forName(driverClassName);
		Connection conn = DriverManager.getConnection("jdbc:mysql://" + server + ":" + port + "/", user,
			passwd);
		Statement st = null;
		try {
			st = conn.createStatement();
			ResultSet rs = st.executeQuery("SHOW DATABASES");
			while(rs.next())
				if (rs.getString(1).equals(database))
					return true;
			return false;
		} finally {
			if (st != null)
				try {
					st.close();
				} catch(SQLException ex) {
					ex.printStackTrace();
				}
			if (conn != null)
				try {
					conn.close();
				} catch(SQLException ex) {
					ex.printStackTrace();
				}
		}
	}
}